package com.example.outdoor;

import java.util.HashSet;
import java.util.Set;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DBHelperVerification extends SQLiteOpenHelper {

	private static final int DB_VERSION = 1;
	private static final String DB_NAME = "OutdoorAppDB.db";
	private static final String TABLE_NAME = "tblOBSTRUCTION";
	private static final String _id = "id";
	private static final String Obstruction = "Obstruction";

	public DBHelperVerification(Context context) {
		super(context, DB_NAME, null, DB_VERSION);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		// TODO Auto-generated method stub
		String createTableQuery = "create table IF NOT EXISTS " + TABLE_NAME
				+ "(" + _id + " INTEGER PRIMARY KEY," + Obstruction + " TEXT)";
		db.execSQL(createTableQuery);

	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		db.execSQL("drop table if exists " + TABLE_NAME);

		onCreate(db);

	}

	public void insertData(String ObstructionValue) {
		SQLiteDatabase db = this.getWritableDatabase();

		ContentValues values = new ContentValues();
		values.put(Obstruction, ObstructionValue);

		db.insert(TABLE_NAME, null, values);
		db.close();
	}

	public Set<String> getAllData() {
		Set<String> set = new HashSet<String>();

		String selectQuery = "select * from " + TABLE_NAME;

		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);

		if (cursor.moveToFirst()) {
			do {

				// set.add(cursor.getString(1));
				set.add(cursor.getString(1));
			} while (cursor.moveToNext());
		}

		cursor.close();
		db.close();

		return set;
	}

	public Set<String> getAllProduct() {
		Set<String> set = new HashSet<String>();

		String selectQuery = "select * from tblbillboard ";

		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);

		if (cursor.moveToFirst()) {
			do {

				// set.add(cursor.getString(1));
				set.add(cursor.getString(30).toString().replace("\"", ""));
			} while (cursor.moveToNext());
		}

		cursor.close();
		db.close();

		return set;
	}

	public Set<String> getStartingPointList(String Avenue) {
		Set<String> set = new HashSet<String>();
		String selectQuery = "Select  * from tblBillboard where Avenue = "
				+ Avenue;
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);
		if (cursor.moveToFirst()) {
			do {
				String str = cursor.getString(28);
				String[] rowData = null;
				if (str.contains(" ")) {
					rowData = str.split(" ");
					set.add(rowData[0].toString().replace("\"", ""));
				} else {
					set.add(str);

				}

			} while (cursor.moveToNext());
		}
		cursor.close();
		db.close();
		return set;
	}

	public Set<String> getAllCarrier() {
		Set<String> set = new HashSet<String>();

		String selectQuery = "select * from tblbillboard ";

		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);

		if (cursor.moveToFirst()) {
			do {

				// set.add(cursor.getString(1));
				set.add(cursor.getString(7).toString().replace("\"", ""));
			} while (cursor.moveToNext());
		}

		cursor.close();
		db.close();

		return set;
	}

	public Set<String> getAllProductCode() {
		Set<String> set = new HashSet<String>();

		String selectQuery = "select * from tblbillboard ";

		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);

		if (cursor.moveToFirst()) {
			do {

				// set.add(cursor.getString(1));
				set.add(cursor.getString(29).toString().replace("\"", ""));
			} while (cursor.moveToNext());
		}

		cursor.close();
		db.close();

		return set;
	}

	public Set<String> getAllVersion(String product) {
		Set<String> set = new HashSet<String>();

		String selectQuery = "select * from tblbillboard where Branddescription like '%"
				+ product + "%'";

		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);

		if (cursor.moveToFirst()) {
			do {

				// set.add(cursor.getString(1));
				set.add(cursor.getString(19).toString().replace("\"", ""));
			} while (cursor.moveToNext());
		}

		cursor.close();
		db.close();

		return set;
	}
	public Set<String> getAllProductByCategory(String CategoryDescription ) {
		Set<String> set = new HashSet<String>();

		String selectQuery = "select ProductName  from tblProduct  where CategoryDescription  like '%"
				+ CategoryDescription  + "%'";

		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);

		if (cursor.moveToFirst()) {
			do {

				// set.add(cursor.getString(1));
				set.add(cursor.getString(0).toString().replace("\"", ""));
			} while (cursor.moveToNext());
		}

		cursor.close();
		db.close();

		return set;
	}
	public Set<String> getAllVersionByCategory(String CategoryDescription ) {
		Set<String> set = new HashSet<String>();

		String selectQuery = "select VersionName  from tblProduct  where ProductName   like '%"
				+ CategoryDescription  + "%'";

		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);

		if (cursor.moveToFirst()) {
			do {

				// set.add(cursor.getString(1));
				set.add(cursor.getString(0).toString().replace("\"", ""));
			} while (cursor.moveToNext());
		}

		cursor.close();
		db.close();

		return set;
	}
	public String getAllVersionCodeByCategory(String CategoryDescription ) {
	

		String selectQuery = "select VersionCode  from tblProduct  where VersionName   like '%"
				+ CategoryDescription  + "%' limit 1";

		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);
String code="";
		if (cursor.moveToFirst()) {
			do {
				code=cursor.getString(0).toString().replace("\"", "");
				// set.add(cursor.getString(1));
				//set.add(cursor.getString(0).toString().replace("\"", ""));
			} while (cursor.moveToNext());
		}

		cursor.close();
		db.close();

		return code;
	}
	public String getAllProductCodeByCategory(String CategoryDescription ) {
		

		String selectQuery = "select ProductCode   from tblProduct  where ProductName   like '%"
				+ CategoryDescription  + "%' limit 1";

		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);
String code="";
		if (cursor.moveToFirst()) {
			do {
				code=cursor.getString(0).toString().replace("\"", "");
				// set.add(cursor.getString(1));
				//set.add(cursor.getString(0).toString().replace("\"", ""));
			} while (cursor.moveToNext());
		}

		cursor.close();
		db.close();

		return code;
	}
	public Set<String> getAllVersionCode(String productCode) {
		Set<String> set = new HashSet<String>();

		String selectQuery = "select * from tblbillboard where BrandCode like '%"
				+ productCode + "%'";

		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);

		if (cursor.moveToFirst()) {
			do {

				// set.add(cursor.getString(1));
				set.add(cursor.getString(18).toString().replace("\"", ""));
			} while (cursor.moveToNext());
		}

		cursor.close();
		db.close();

		return set;
	}

	public String getProductCode(String product) {
		Set<String> set = new HashSet<String>();

		String selectQuery = "select * from tblbillboard where Branddescription like '%"
				+ product + "%' limit 1";

		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);
		String productCode = "";
		if (cursor.moveToFirst()) {
			do {

				// set.add(cursor.getString(1));
				productCode = cursor.getString(29).toString().replace("\"", "");
			} while (cursor.moveToNext());
		}

		cursor.close();
		db.close();

		return productCode;
	}

	public String getProduct(String product) {
		Set<String> set = new HashSet<String>();

		String selectQuery = "select * from tblbillboard where BrandCode like '%"
				+ product + "%' limit 1";

		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);
		String productDescription = "";
		if (cursor.moveToFirst()) {
			do {

				// set.add(cursor.getString(1));
				productDescription = cursor.getString(30).toString()
						.replace("\"", "");
			} while (cursor.moveToNext());
		}

		cursor.close();
		db.close();

		return productDescription;
	}

	public String getVersionCode(String Version) {
		Set<String> set = new HashSet<String>();

		String selectQuery = "select * from tblbillboard where Versiondescription like '%"
				+ Version + "%' limit 1";

		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);
		String productCode = "";
		if (cursor.moveToFirst()) {
			do {

				// set.add(cursor.getString(1));
				productCode = cursor.getString(18).toString().replace("\"", "");
			} while (cursor.moveToNext());
		}

		cursor.close();
		db.close();

		return productCode;
	}

	public String getVersion(String Version) {
		Set<String> set = new HashSet<String>();

		String selectQuery = "select * from tblbillboard where VersionCode like '%"
				+ Version + "%' limit 1";

		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);
		String versionDescription = "";
		if (cursor.moveToFirst()) {
			do {

				// set.add(cursor.getString(1));
				versionDescription = cursor.getString(19).toString()
						.replace("\"", "");
			} while (cursor.moveToNext());
		}

		cursor.close();
		db.close();

		return versionDescription;
	}

	public Set<String> getAllDataOfCondition() {
		Set<String> set = new HashSet<String>();

		String selectQuery = "select * from tblCondition";

		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);

		if (cursor.moveToFirst()) {
			do {

				// set.add(cursor.getString(1));
				set.add(cursor.getString(1));
			} while (cursor.moveToNext());
		}

		cursor.close();
		db.close();

		return set;
	}

	public String ProductForNewRecord() {
		SQLiteDatabase localSQLiteDatabase = getReadableDatabase();
		Cursor localCursor = localSQLiteDatabase.rawQuery(
				"select max(Product) from tblNewProduct", null);
		String str = "";
		if (localCursor.moveToFirst())
			do{
				str = localCursor.getString(0);
			}while (localCursor.moveToNext());
		localCursor.close();
		localSQLiteDatabase.close();
		if (str == null)
			return "900001";
		else
			return String.valueOf(1 + Integer.parseInt(str));
	}

	public String ImageForNewRecord(String paramString) {
		SQLiteDatabase localSQLiteDatabase = getReadableDatabase();
		localSQLiteDatabase
				.execSQL("CREATE TABLE IF NOT EXISTS tblImageName(ID INTEGER PRIMARY KEY , Avenue INTEGER,ImageCode VARCHAR); ");
		Cursor localCursor = localSQLiteDatabase.rawQuery(
				"select max(Imagecode)  from tblImageName where Avenue like '%"
						+ paramString + "'", null);
		String str = "";
		if (localCursor.moveToFirst())
			do
				str = localCursor.getString(0);
			while (localCursor.moveToNext());
		localCursor.close();
		localSQLiteDatabase.close();
		if (str == null)
			return paramString + "0001";
		else
			return String.valueOf(1 + Long.parseLong(str));
	}

	public String VersionForNewRecord() {
		SQLiteDatabase localSQLiteDatabase = getReadableDatabase();
		Cursor localCursor = localSQLiteDatabase.rawQuery(
				"select max(Version)  from tblNewProduct", null);
		String str = "";
		if (localCursor.moveToFirst())
			do
				str = localCursor.getString(0);
			while (localCursor.moveToNext());
		localCursor.close();
		localSQLiteDatabase.close();
		if (str == null)
			return "120001";
		else
			return String.valueOf(1 + Integer.parseInt(str));
	}

	public int lastID() {
		SQLiteDatabase localSQLiteDatabase = getReadableDatabase();
		Cursor localCursor = localSQLiteDatabase.rawQuery(
				"Select max(id) from tblBillboard", null);
		boolean bool = localCursor.moveToFirst();
		int i = 0;
		if (bool)
			do
				i = Integer.parseInt(localCursor.getString(0));
			while (localCursor.moveToNext());
		localCursor.close();
		localSQLiteDatabase.close();
		return i;
	}

	public Set<String> getAllData1() {
		Set<String> set = new HashSet<String>();

		SQLiteDatabase db = getReadableDatabase();

		Cursor cursor = db.rawQuery(
				"select distinct CategoryDescription from tblProduct", null);

		if (cursor.moveToFirst()) {
			do {

				set.add(cursor.getString(0).toString().replace("\"", ""));
			} while (cursor.moveToNext());
		}

		cursor.close();
		db.close();

		return set;

	}

}
